Excel Pivot Table creation

HCL Notes/Domino 8.5 Forum (includes Notes Traveler)

Subject: Excel Pivot Table creation

Feedback Type: Question

Product Area: Notes Client

Technical Area: Application Development

Platform: Windows

Release: 8.5.3

Reproducible: Unknown

I'm trying to generate an Excel Pivot Table in an Excel report using an agent. I have followed much of the code in the example provided in an earlier post (See Below).

http://www-10.lotus.com/ldd/46dom.nsf/DateAllFlatweb/0e798be236e69f4f8525765d004ec027?OpenDocument

I'm using Excel 2010 and Notes 8.5.3.

Below is the code I'm using to create the spreadsheet and create the report. If I don't call the sub to generate the report the Excel file is created without a problem. (Some code was not included to remove the clutter.)

Declarations:
Dim xl As Variant, xlWbk As Variant, xlWbk2 As Variant, xlWbk3 As Variant, xlWbk4 As Variant, xlWbk5 As Variant
Dim xlSheet As Variant, xlSheet2 As Variant

Initialize
Set xl = CreateObject("Excel.application")
If choice = promptlist(0) Or choice = promptlist(1) Then
Set xlWbk = xl.Workbooks.Add
Else
Set xlWbk = xl.Workbooks.Open(xlsFileName(0))
End If
Set xlSheet = xlWbk.Worksheets(1)

Set xlSheet2 = xlWbk.Worksheets(2)
Call xlSheet2.Activate
On Error Goto errorHandler
xlSheet2.Name = "Analysis by Inspection Status"

Sub createPivotTable

Dim pivotcache1 As Variant
Dim tmppivotcaches As Variant
Dim xlpivottable As Variant
Dim xlpivotfield As Variant

Set tmppivotcaches = xlWbk.pivotcaches()

Set pivotcache1 = tmppivotcaches.add(1,"Inspection Data!R2C1:R6C43")
'Row 2 is the column headers

'problem line
Set xlpivottable = pivotcache1.createpivottable(xlSheet2.range("A5"),"pivot1")

'Set xlpivottable = pivotcache1.createpivottable("Analysis by Inspection Status!R2C1","pivot1")

Set xlpivotfield = xlpivottable.pivotfields("Building Name")

xlpivotfield.orientation = 4
xlpivotfield.function = 1

Set xlpivotfield =xlpivottable.pivotfields("Inspection Status")

xlpivotfield.orientation = 1
xlpivotfield.subtotals(1) = True
xlpivotfield.subtotals(1) = False

end Sub

The code blows up on the line to create the Pivot Table:
Set xlpivottable = pivotcache1.createpivottable(xlSheet2.range("A5"),"pivot1")

I get an error "The PivotTable field name is not valid.... when agent hits this line. When I change the agent to run the other line of code ( Set xlpivottable = pivotcache1.createpivottable("Analysis by Inspection Status!R2C1","pivot1") ) I get an OLE: Automation Object Error and the code bombs.

I have looked over all post concerning these errors and how to create a Pivot Table and couldn't find anything that would help and thought I would see if anyone had any suggestions.

Thanks!!!

Feedback number WEBB9GMNGD created by ~Bill Zekboosichekflar on 02/24/2014

Status: Closed
Comments: